package org.szx.dao;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.type.StringType;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.szx.model.ExamSubject;
import org.szx.model.ExamUser;

@Repository
public class SubjectDaoImpl extends BaseHibernateDAO<ExamSubject> implements SubjectDao {
	
	@Override
	@Transactional
	public List<ExamSubject> findALLSubjects() {
		return this.loadAll();
	}

	@Override
	@Transactional
	public void createSubject(ExamSubject subject) {
		this.save(subject);
	}

	@Override
	@Transactional
	public ExamSubject getSubByID(Integer sub_id) {
		return this.get(sub_id);
	}

	@Override
	@Transactional
	public void editSubject(ExamSubject subject) {
		this.merge(subject);
	}

	@Override
	@Transactional
	public void deleteUser(Integer sub_id) {
		this.deleteByKey(sub_id);
	}

	@Override
	@Transactional
	public List<ExamUser> findAllUsers() {
		Session session = getSession();
		String hql = "from ExamUser";
		@SuppressWarnings("unchecked")
		List<ExamUser> examUser = session.createQuery(hql).list();
		return examUser;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional
	public List<String> getAllUserBySubject(Integer subjectId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select user.user_id as userId ");
		sql.append(" from exam_user_subject eus ");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id ");
		sql.append(" left join exam_user user on user.user_id = eus.user_id ");
		sql.append(" where es.sub_id = :subjectId ");
		return this.createSqlQuery(String.valueOf(sql))
				.addScalar("userId",StringType.INSTANCE)
				.setInteger("subjectId", subjectId)
				.list();
	}

	@Override
	@Transactional
	public void insertSubjectUsers(List<String> insertList,Integer subjectId){
		String sql = null;
		for(String userId:insertList){
			sql = "insert into exam_user_subject(user_id, sub_id) values(:userId, :subjectId)";
			this.createSqlQuery(sql).setString("userId", userId).setInteger("subjectId", subjectId).executeUpdate();
			
		}
	}

	@Override
	@Transactional
	public void deleteSubjectUsers(List<String> delList,Integer subjectId){
		String sql = null;
		for(String userId:delList){
			sql = "delete  exam_user_subject from exam_user_subject where user_id = :userId and sub_id = :subjectId";
			this.createSqlQuery(sql).setString("userId", userId).setInteger("subjectId", subjectId).executeUpdate();
			
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> findALLSubjectsByUser(String userId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select es.sub_id as subId ");
		sql.append(" from exam_user_subject eus ");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id ");
		sql.append(" left join exam_user user on user.user_id = eus.user_id ");
		sql.append(" where user.user_id = :userId ");
		return this.createSqlQuery(String.valueOf(sql))
				.addScalar("subId",StringType.INSTANCE)
				.setString("userId", userId)
				.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> findAllScoreByUser(String userId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select subject.sub_id as subId ");
		sql.append(" from exam_score es ");
		sql.append(" left join exam_user eu on es.user_id = eu.user_id ");
		sql.append(" left join exam_subject subject on subject.sub_id= es.subject_id ");
		sql.append(" where eu.user_id = :userId ");
		return this.createSqlQuery(String.valueOf(sql))
				.addScalar("subId", StringType.INSTANCE)
				.setString("userId", userId).list();

	}
	
	@Override
	@Transactional
	public void insertUserSubjects(List<String> insertList,String userId){
		String sql = null;
		for(String subId:insertList){
			sql = "insert into exam_user_subject(user_id, sub_id) values(:userId, :subjectId)";
			this.createSqlQuery(sql).setString("userId", userId).setString("subjectId", subId).executeUpdate();
			
		}
	}

	@Override
	@Transactional
	public void deleteUserSubjects(List<String> delList,String userId){
		String sql = null;
		for(String subId:delList){
			sql = "delete exam_user_subject from exam_user_subject where user_id = :userId and sub_id = :subjectId";
			this.createSqlQuery(sql).setString("userId", userId).setString("subjectId", subId).executeUpdate();
			
		}
	}
}
